﻿using MiniExcelLibs;

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace KDAcctAttach {

    public partial class FrmQuery : Form {

        public FrmQuery()
        {
            InitializeComponent();
        }

        private DataTable _dt;
        private List<DBItemModel> _dbList;

        #region 启用/禁用界面

        private void EnBtn(bool s)
        {
            void eb()
            {
                btnGetDBList.Enabled = s;
                btnQuery.Enabled = s;
                btnCommand.Enabled = s;
                btnExportResult.Enabled = s;
                tbxQuery.ReadOnly = !s;
                dgvDBList.Enabled = s;
                dgvQueryResult.Enabled = s;
            }
            if (InvokeRequired)
                Invoke(new MethodInvoker(() => eb()));
            else
                eb();
        }

        #endregion 启用/禁用界面

        #region 窗口载入

        private void FrmQuery_Shown(object sender, EventArgs e)
        {
            _dt = new DataTable();
            _dbList = new List<DBItemModel>();
            bsDBList.DataSource = _dbList;
        }

        #endregion 窗口载入

        #region 导出结果

        private void BtnExportResult_Click(object sender, EventArgs e)
        {
            try
            {
                if (_dt is null)
                    throw new Exception("没有数据");
                if (_dt.Rows.Count < 1)
                    throw new Exception("没有数据");
                SaveFileDialog sf = new SaveFileDialog();
                sf.Filter = "Excel2007|*.xlsx";
                if (sf.ShowDialog() != DialogResult.OK)
                    return;
                EnBtn(false);
                MiniExcel.SaveAs(sf.FileName, _dt, overwriteFile: true, excelType: ExcelType.XLSX);
                EnBtn(true);
                MessageBox.Show("OK");
            }
            catch (Exception ex)
            {
                EnBtn(true);
                MessageBox.Show(ex.Message);
            }
        }

        #endregion 导出结果

        #region 获取数据库列表

        private async void BtnGetDBList_Click(object sender, EventArgs e)
        {
            try
            {
                EnBtn(false);
                _dbList.Clear();
                bsDBList.ResetBindings(false);
                var db = DB.Inst();
                DB.GetConn(db, "master");
                var err = await Task.Run(() =>
                {
                    try
                    {
                        var lst = db.Ado.SqlQuery<DBItemModel>("select name 'DBName',dbid 'DBId',cmptlevel 'CMPTLevel',version 'DBVersion',filename 'DBPath' from sysdatabases;");
                        if (lst is null)
                            return null;
                        if (lst.Count < 1)
                            return null;
                        _dbList.AddRange(lst);
                        return null;
                    }
                    catch (Exception ex)
                    {
                        return ex.Message;
                    }
                });
                if (!string.IsNullOrEmpty(err))
                    throw new Exception(err);
                bsDBList.ResetBindings(false);
                EnBtn(true);
            }
            catch (Exception ex)
            {
                EnBtn(true);
                MessageBox.Show(ex.Message);
            }
        }

        #endregion 获取数据库列表
        #region 获取选择的数据库名
        private string GetSelectedDBName()
        {
            if(dgvDBList.SelectedRows.Count<1)
                throw new Exception("必须选择数据库");
            string name = dgvDBList.Rows[dgvDBList.SelectedRows[0].Index].Cells["colDBName"].Value.ToString();
            return name;
        }
        #endregion
        #region 执行查询
        private async void BtnQuery_Click(object sender, EventArgs e)
        {
            try
            {
                string sqlstr = tbxQuery.SelectedText;
                if (CheckEmpty(sqlstr))
                    sqlstr = tbxQuery.Text;
                if (CheckEmpty(sqlstr))
                    return;
                tbxResult.Text = "";
                EnBtn(false);
                sqlstr = sqlstr.Trim();
                string dbName = GetSelectedDBName();
                var db = DB.Inst();
                DB.GetConn(db, dbName);
                //dgvQueryResult.Rows.Clear();
                //dgvQueryResult.Columns.Clear();
                dgvQueryResult.DataSource = null;
                DataTable dt = new DataTable();
                var err = await Task.Run(() =>
                {
                    try
                    {
                        dt = db.Ado.GetDataTable(sqlstr);
                        return null;
                    }
                    catch (Exception ex)
                    {
                        return ex.Message;
                    }
                });
                if(!CheckEmpty(err))
                    throw new Exception(err);
                _dt = dt;
                tbxResult.Text = "查询完成";
                dgvQueryResult.DataSource = _dt;
                dgvQueryResult.AutoResizeColumns();
                EnBtn(true);
            }
            catch (Exception ex)
            {
                EnBtn(true);
                tbxResult.AppendText(ex.Message + Environment.NewLine);
            }
        }
        private bool CheckEmpty(string sqlstr)
        {
            if (string.IsNullOrEmpty(sqlstr))
                return true;
            if (string.IsNullOrEmpty(sqlstr.Trim()))
                return true;
            return false;
        }
        #endregion
        #region 执行命令
        private async void BtnCommand_Click(object sender, EventArgs e)
        {
            try
            {
                string sqlstr = tbxQuery.SelectedText;
                if (CheckEmpty(sqlstr))
                    sqlstr = tbxQuery.Text;
                if (CheckEmpty(sqlstr))
                    return;
                tbxResult.Text = "";
                EnBtn(false);
                sqlstr = sqlstr.Trim();
                string dbName = GetSelectedDBName();
                var db = DB.Inst();
                DB.GetConn(db, dbName);
                dgvQueryResult.Rows.Clear();
                dgvQueryResult.Columns.Clear();
                int rows = 0;
                var err = await Task.Run(() =>
                {
                    try
                    {
                        rows = db.Ado.ExecuteCommand(sqlstr);
                        return null;
                    }
                    catch (Exception ex)
                    {
                        return ex.Message;
                    }
                });
                if (!CheckEmpty(err))
                    throw new Exception(err);
                tbxResult.Text = $"执行完毕,影响行数{rows}";
                dgvQueryResult.DataSource = _dt;
                dgvQueryResult.AutoResizeColumns();
                EnBtn(true);
            }
            catch (Exception ex)
            {
                EnBtn(true);
                tbxResult.AppendText(ex.Message + Environment.NewLine);
            }
        }
        #endregion

        private void DgvQueryResult_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            e.Cancel = true;
        }
    }
}